﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
using BCCommon;
using BCCommon.Enums;
using BCCommon.OAEnums;
using BCEntity.OA.TeamWorks.TaskRecord;

namespace BCData.OA.TeamWorks.TaskRecord
{
    public class TaskRecordData : ITaskRecordData
    {
        private const string QUERY = @"SELECT TaskRecordNo,BusinessTypeName,BusinessType,WorkflowActivityInstanceId,CompanyId,
                                              TaskCategory,HandleStatus,PauseTime,PauseRemark,RelevantTaskRecordNo,CurrentlyStep,CreateTime,TaskExecuterAdminId,
                                              TaskExecuterFullName,StartTime,CompleteTime,TaskRemark,RoleType 
                                       FROM OA_TaskRecord";
        private const string QUERY_JOIN = @"SELECT A.TaskRecordNo,A.WorkflowActivityInstanceId,A.TaskCategory,A.BusinessType,A.BusinessTypeName,A.TaskRemark,A.HandleStatus,
                                                   A.PauseTime,A.PauseRemark,A.RelevantTaskRecordNo,A.CurrentlyStep,A.CreateTime,A.StartTime,A.TaskExecuterAdminId,A.TaskExecuterFullName,
                                                   A.CompleteTime,A.CompanyId,A.RoleType,B.StepType
                                            FROM OA_TaskRecord A 
                                            INNER JOIN OA_WorkflowActivityStepInstance B ON B.WorkflowActivityInstanceId=A.WorkflowActivityInstanceId AND B.TaskStep=A.CurrentlyStep";
        private const string UPDATE = @"UPDATE `oa_taskrecord`
SET
    `WorkflowActivityInstanceId` = ?WorkflowActivityInstanceId,
    `TaskCategory` = ?TaskCategory,
    `BusinessType` = ?BusinessType,
    `BusinessTypeName` = ?BusinessTypeName,
    `HandleStatus` = ?HandleStatus,
    `PauseTime` = ?PauseTime,
    `PauseRemark` = ?PauseRemark,
    `RelevantTaskRecordNo` = ?RelevantTaskRecordNo,
    `CurrentlyStep` = ?CurrentlyStep,
    `StartTime` = ?StartTime,
    `TaskExecuterAdminId` = ?TaskExecuterAdminId,
    `TaskExecuterFullName` = ?TaskExecuterFullName,
    `CompleteTime` = ?CompleteTime,
    `TaskRemark` = ?TaskRemark,
    `CompanyId` = ?CompanyId,
    `RoleType` = ?RoleType,
    `CreateTime` = ?CreateTime
WHERE `TaskRecordNo` = ?TaskRecordNo";

        private readonly IDatabaseContext databaseContext;

        public TaskRecordData(IDatabaseContext databaseContext)
        {
            this.databaseContext=databaseContext;
        }

        #region 查询操作

        public async Task<TaskRecordEntity> GetEntity(string taskRecordNo)
        {
            string sql = string.Format("{0} WHERE TaskRecordNo=?TaskRecordNo", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskRecordNo", taskRecordNo, DbType.AnsiString, size: 50);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<TaskRecordEntity>(sql, parameters);
        }

        public async Task<IEnumerable<TaskRecordEntity>> Get(long workflowActivityInstanceId)
        {
            string sql = string.Format("{0} WHERE WorkflowActivityInstanceId=?WorkflowActivityInstanceId ORDER BY CreateTime DESC", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?WorkflowActivityInstanceId", workflowActivityInstanceId, DbType.Int64, size: 20);
            return await this.databaseContext.Slave.QueryAsync<TaskRecordEntity>(sql, parameters);
        }

        /// <summary>
        /// 分页获取当前人员下的任务列表
        /// </summary>
        /// <param name="executerAdminId">执行人Id</param>
        /// <param name="start">开始日期</param>
        /// <param name="end">结束日期</param>
        /// <param name="handleStatus">处理状态</param>
        /// <param name="isException"></param>
        /// <param name="sortName">排序字段</param>
        /// <param name="companyId">公司Id</param>
        /// <param name="ascending">是否升序排序</param>
        /// <param name="pageIndex">分页页码</param>
        /// <param name="pageSize">分页页数</param>
        /// <param name="taskStep">任务步骤</param>
        /// <param name="isMyApproved">是否我审批的</param>
        /// <returns></returns>
        public async Task<(IEnumerable<TaskRecordEntity>, int)> Query(long? executerAdminId, DateTime? start, DateTime? end, OATaskHandleStatus? handleStatus, string sortName, int? companyId, bool? ascending, int pageIndex, int pageSize, int? taskStep = null, bool? isMyApproved = null)
        {
            List<string> wheres = new List<string>()
            {
                "RoleType = 'Admin'"
            };

            DynamicParameters parameters = new DynamicParameters();

            if (companyId.HasValue)
            {
                wheres.Add("CompanyId =?CompanyId");
                parameters.Add("CompanyId", companyId.Value, DbType.Int32, size: 20);
            }

            if (start.HasValue)
            {
                wheres.Add("CreateTime>=?StartTime");
                parameters.Add("?StartTime", start.Value, DbType.DateTime);
            }

            if (end.HasValue)
            {
                wheres.Add("CreateTime<=?EndTime");
                parameters.Add("?EndTime", end.Value, DbType.DateTime);
            }

            if (executerAdminId.HasValue)
            {
                wheres.Add("TaskExecuterAdminId=?TaskExecuterAdminId");
                parameters.Add("?TaskExecuterAdminId", executerAdminId.Value, DbType.Int64, size: 20);
            }

            if (handleStatus.HasValue)
            {
                wheres.Add("HandleStatus=?HandleStatus");
                parameters.Add("?HandleStatus", handleStatus.ToString(), DbType.AnsiString);
            }

            if (taskStep.HasValue)
            {
                wheres.Add("CurrentlyStep=?CurrentlyStep");
                parameters.Add("?CurrentlyStep", taskStep.Value, DbType.Int32);
            }

            if (isMyApproved.HasValue)
            {
                wheres.Add("CurrentlyStep>0");
            }

            if (!taskStep.HasValue && !isMyApproved.HasValue)
            {
                wheres.Add(" TaskCategory <> ?TaskCategory");
                parameters.Add("?TaskCategory", OATaskCategoryType.Finance.ToString(), DbType.AnsiString);
            };

            string sql = string.Format(@"{0} {1} ORDER BY {2} {3} LIMIT {4},{5}; 
                    SELECT COUNT(1)  FROM  OA_TaskRecord {1}",
                    QUERY,
                    wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
                    !string.IsNullOrWhiteSpace(sortName) ? sortName : "CreateTime",
                    ascending == true ? "ASC" : "DESC",
                    (pageIndex - 1) * pageSize,
                    pageSize);

            return await this.databaseContext.Slave.QueryPageableListAsync<TaskRecordEntity>(sql, parameters);
        }
        #endregion

        #region 修改操作


        public async Task<bool> UpdateExecuterAdminInfo(TaskRecordEntity taskRecordEntity)
        {
            string sql = SqlCommands.GetUpdateSql("OA_TaskRecord", new string[] { "TaskExecuterAdminId", "TaskExecuterFullName", "TaskRemark", "RoleType" }, new string[] { "TaskRecordNo" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskRecordNo", taskRecordEntity.TaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?TaskExecuterAdminId", taskRecordEntity.TaskExecuterAdminId, DbType.Int64, size: 20);
            parameters.Add("?TaskExecuterFullName", taskRecordEntity.TaskExecuterFullName, DbType.AnsiString, size: 20);
            parameters.Add("?TaskRemark", taskRecordEntity.TaskRemark, DbType.AnsiString);
            parameters.Add("?RoleType", taskRecordEntity.RoleType.ToString(), DbType.AnsiString);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }


        public async Task<bool> UpdateStartTime(string taskRecordNo, DateTime? startTime)
        {
            string sql = SqlCommands.GetUpdateSql("OA_TaskRecord", new string[] { "StartTime" }, new string[] { "TaskRecordNo" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskRecordNo", taskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?StartTime", startTime.HasValue ? startTime.Value : DateTime.Now, DbType.DateTime);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        #endregion

        #region 检查任务记录是否存在
        public async Task<bool> CheckExists(string taskRecordNo)
        {
            string sql = @"SELECT 1 FROM OA_TaskRecord WHERE TaskRecordNo=?TaskRecordNo LIMIT 1";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskRecordNo", taskRecordNo, DbType.AnsiString, size: 50);
            return await this.databaseContext.Slave.ExecuteScalarAsync(sql, parameters) != null;
        }
        #endregion

        #region 更新处理状态
        public async Task<bool> UpdateHandleStatus(string taskRecordNo, OATaskHandleStatus handleStatus)
        {
            string sql = SqlCommands.GetUpdateSql("OA_TaskRecord", new string[] { "HandleStatus", "CompleteTime" }, new string[] { "TaskRecordNo" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?HandleStatus", handleStatus.ToString(), DbType.AnsiString);
            parameters.Add("?CompleteTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?TaskRecordNo", taskRecordNo, DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }
        #endregion

        #region 新增操作


        /// <summary>
        /// 新增一条任务记录信息
        /// </summary>
        /// <param name="entity">任务记录</param>
        /// <param name="receiverEntities">任务参与者</param>
        /// <returns></returns>
        public async Task<TaskRecordEntity> InsertAsync(TaskRecordEntity entity, IEnumerable<TaskReceiverRecordEntity> receiverEntities = null)
        {
            List<string> sqls = new List<string>();
            List<DynamicParameters> parametersList = new List<DynamicParameters>();
            sqls.Add(this.GetInsertTaskRecordSql(entity, out DynamicParameters parameters));
            parametersList.Add(parameters);

            if (receiverEntities != null && receiverEntities.Any())
            {
                foreach (var receiver in receiverEntities)
                {
                    sqls.Add(this.GetInsertTaskReceiverRecordSql(receiver, out parameters));
                    parametersList.Add(parameters);
                }
            }
            int result = this.databaseContext.Master.TransactionExecuteAsync(sqls, parametersList, databaseContext.Transaction).GetAwaiter().GetResult();
            if (result > 0)
            {
                string querySql = string.Format("{0} Where TaskRecordNo=?TaskRecordNo ", QUERY);
                DynamicParameters parameter = new DynamicParameters();
                parameter.Add("?TaskRecordNo", entity.TaskRecordNo, DbType.AnsiString, size: 50);
                return await this.databaseContext.Master.QueryFirstOrDefaultAsync<TaskRecordEntity>(querySql, parameter);
            }
            return new TaskRecordEntity();
        }

        /// <summary>
        /// 新增一条任务参与者记录
        /// </summary>
        /// <param name="receiver">参与人</param>
        /// <param name="parameters">参与信息</param>
        /// <returns></returns>
        private string GetInsertTaskReceiverRecordSql(TaskReceiverRecordEntity receiver, out DynamicParameters parameters)
        {
            string sql = SqlCommands.GetInsertSql("OA_TaskReceiverRecord", new string[] { "WorkflowActivityInstanceId", "TaskStep", "TaskRecordNo", "ReceiverAdminId", "ReceiverAdminFullName", "ReceiverRoleType", "ReceiverRole", "CreateTime" });

            parameters = new DynamicParameters();

            parameters.Add("?WorkflowActivityInstanceId", receiver.WorkflowActivityInstanceId, DbType.Int64);
            parameters.Add("?TaskStep", receiver.TaskStep, DbType.Int32);
            parameters.Add("?TaskRecordNo", receiver.TaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?ReceiverAdminId", receiver.ReceiverAdminId, DbType.Int64);
            parameters.Add("?ReceiverAdminFullName", receiver.ReceiverAdminFullName, DbType.AnsiString, size: 20);
            parameters.Add("?ReceiverRoleType", receiver.ReceiverRoleType.ToString(), DbType.AnsiString, size: 20);
            parameters.Add("?ReceiverRole", receiver.ReceiverRole.ToString(), DbType.AnsiString);
            parameters.Add("?CreateTime", receiver.CreateTime, DbType.DateTime);

            return sql;
        }

        /// <summary>
        /// 新增一条任务记录
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private string GetInsertTaskRecordSql(TaskRecordEntity entity, out DynamicParameters parameters)
        {
            string sql = SqlCommands.GetInsertSql("OA_TaskRecord", new string[] { "WorkflowActivityInstanceId","TaskRecordNo", "TaskCategory", "BusinessType", "BusinessTypeName", "HandleStatus","StartTime","TaskExecuterAdminId","TaskExecuterFullName","CompleteTime","CompanyId",
                "RelevantTaskRecordNo","CurrentlyStep","CreateTime","TaskRemark","RoleType"});

            parameters = new DynamicParameters();

            parameters.Add("?WorkflowActivityInstanceId", entity.WorkflowActivityInstanceId, DbType.Int64, size: 20);
            parameters.Add("?TaskRecordNo", entity.TaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?TaskCategory", entity.TaskCategory.ToString(), DbType.AnsiString);
            parameters.Add("?BusinessType", entity.BusinessType.ToString(), DbType.AnsiString, size: 150);
            parameters.Add("?BusinessTypeName", entity.BusinessTypeName, DbType.AnsiString, size: 50);
            parameters.Add("?HandleStatus", entity.HandleStatus.ToString(), DbType.AnsiString);
            parameters.Add("?RelevantTaskRecordNo", entity.RelevantTaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?StartTime", entity.StartTime, DbType.Date);
            parameters.Add("?CurrentlyStep", entity.CurrentlyStep, DbType.Int32, size: 10);
            parameters.Add("?TaskExecuterAdminId", entity.TaskExecuterAdminId ?? default(long?), DbType.Int64);
            parameters.Add("?TaskExecuterFullName", entity.TaskExecuterFullName, DbType.AnsiString, size: 20);
            parameters.Add("?CompleteTime", entity.CompleteTime, DbType.DateTime);
            parameters.Add("?CreateTime", entity.CurrentlyStep == 0 ? DateTime.Now.AddSeconds(-2) : DateTime.Now, DbType.DateTime);
            parameters.Add("?CompanyId", entity.CompanyId, DbType.Int32, size: 10);
            parameters.Add("?TaskRemark", entity.TaskRemark, DbType.AnsiString);
            parameters.Add("?RoleType", entity.RoleType.HasValue ? entity.RoleType.ToString() : "Admin", DbType.AnsiString);

            return sql;
        }

        /// <summary>
        /// 根据活动实例对应的业务单据编号查询任务记录信息
        /// </summary>
        /// <param name="applyCode">业务单据编号</param>
        /// <returns></returns>
        public async Task<IEnumerable<TaskRecordEntity>> GetTaskRecordEntitiesByReferenceNoAsync(string applyCode)
        {
            string sql = @"SELECT 
                            C.TaskRecordNo,
                            C.BusinessTypeName,
                            C.BusinessType,
                            C.WorkflowActivityInstanceId,
                            C.TaskCategory,
                            C.HandleStatus,
                            C.PauseTime,
                            C.PauseRemark,
                            C.RelevantTaskRecordNo,
                            C.CurrentlyStep,
                            C.CreateTime,
                            C.TaskExecuterAdminId,
                            C.TaskExecuterFullName,
                            C.StartTime,
                            C.CompleteTime,
                            C.TaskRemark,
                            C.CompanyId,
                            C.RoleType
                             FROM OA_WorkflowActivityInstanceReference A 
                            LEFT JOIN OA_WorkflowActivityInstance B ON A.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId 
                            LEFT JOIN OA_TaskRecord C ON B.WorkflowActivityInstanceId=C.WorkflowActivityInstanceId  ";
            DynamicParameters parameters = new DynamicParameters();
            sql += "where A.ReferenceNo=?ReferenceNo ";
            parameters.Add("?ReferenceNo", applyCode, DbType.AnsiString, size: 50);

            return (await this.databaseContext.Slave.QueryAsync<TaskRecordEntity>(sql, parameters)).OrderBy(s => s.CurrentlyStep);
        }

        public async Task<TaskRecordEntity> GetLatestRecordAsync(long workflowActivityInstanceId)
        {
            string sql = string.Format("{0} WHERE WorkflowActivityInstanceId=?WorkflowActivityInstanceId ORDER BY CreateTime DESC LIMIT 1", QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?WorkflowActivityInstanceId", workflowActivityInstanceId, DbType.Int64, size: 20);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<TaskRecordEntity>(sql, parameters);
        }

        /// <summary>
        /// 获取活动实例下的任务列表
        /// </summary>
        /// <param name="workflowActivityInstanceId"></param>
        /// <param name="taskStep"></param>
        /// <returns></returns>
        public async Task<IEnumerable<TaskRecordEntity>> GetByWorkflowActivityInstanceIdAsync(long workflowActivityInstanceId, int? taskStep = null)
        {
            string sql = $"{QUERY} WHERE WorkflowActivityInstanceId = ?WorkflowActivityInstanceId";

            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?WorkflowActivityInstanceId", workflowActivityInstanceId, DbType.Int64);

            if (taskStep.HasValue)
            {
                sql += " AND CurrentlyStep = ?CurrentlyStep ";
                parameters.Add("?CurrentlyStep", taskStep);
            }

            sql += " ORDER BY CreateTime";

            return await this.databaseContext.Slave.QueryAsync<TaskRecordEntity>(sql, parameters);
        }

        /// <summary>
        /// 根据关联业务单号和执行人查询任务信息
        /// </summary>
        /// <param name="referenceNo">关联业务单号</param>
        /// <param name="adminId">执行任务人</param>
        /// <returns></returns>
        public async Task<TaskRecordEntity> GetByReferenceNoAndTaskExecuterAdminId(string referenceNo, long adminId)
        {
            string sqlJoin = @"Select C.TaskRecordNo,C.BusinessTypeName,C.BusinessType,C.WorkflowActivityInstanceId,C.TaskCategory,C.HandleStatus,C.PauseTime,C.PauseRemark,C.RelevantTaskRecordNo,C.CurrentlyStep,C.CreateTime,C.TaskExecuterAdminId,
                    C.TaskExecuterFullName,C.StartTime,C.CompleteTime,C.CompanyId,C.RoleType,C.TaskRemark  from OA_workflowActivityInstanceReference A
                    LEFT JOIN  OA_WorkflowActivityInstance B ON A.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId
                    LEFT JOIN  OA_TaskRecord C ON C.WorkflowActivityInstanceId=B.WorkflowActivityInstanceId AND B.CurrentlyStep=C.CurrentlyStep ";
            string sql = string.Format("{0} Where A.ReferenceNo=?ReferenceNo AND C.TaskExecuterAdminId=?TaskExecuterAdminId", sqlJoin);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?ReferenceNo", referenceNo.ToString(), DbType.AnsiString);
            parameters.Add("?TaskExecuterAdminId", adminId, DbType.Int64);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<TaskRecordEntity>(sql, parameters);
        }
        #endregion

        public async Task<bool> UpdateHandleStatusAndTaskRemark(string taskRecordNo, OATaskHandleStatus handleStatus, string taskRemark)
        {
            string sql = SqlCommands.GetUpdateSql("OA_TaskRecord", new string[] { "HandleStatus", "CompleteTime", "TaskRemark" }, new string[] { "TaskRecordNo" });
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?HandleStatus", handleStatus.ToString(), DbType.AnsiString);
            parameters.Add("?CompleteTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?TaskRemark", taskRemark, DbType.AnsiString);
            parameters.Add("?TaskRecordNo", taskRecordNo, DbType.AnsiString, size: 50);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, databaseContext.Transaction) > 0;
        }

        public async Task UpdateTaskRecordAsync(TaskRecordEntity lastTaskRecord)
        {
            DynamicParameters parameters = new DynamicParameters();

            parameters.Add("?WorkflowActivityInstanceId", lastTaskRecord.WorkflowActivityInstanceId, DbType.Int64, size: 20);
            parameters.Add("?TaskCategory", lastTaskRecord.TaskCategory.ToString(), DbType.AnsiString);
            parameters.Add("?BusinessType", lastTaskRecord.BusinessType, DbType.AnsiString, size: 150);
            parameters.Add("?BusinessTypeName", lastTaskRecord.BusinessTypeName, DbType.AnsiString, size: 50);
            parameters.Add("?HandleStatus", lastTaskRecord.HandleStatus.ToString(), DbType.AnsiString);
            parameters.Add("?PauseTime", lastTaskRecord.PauseTime, DbType.DateTime);
            parameters.Add("?PauseRemark", lastTaskRecord.PauseRemark, DbType.AnsiString);
            parameters.Add("?RelevantTaskRecordNo", lastTaskRecord.RelevantTaskRecordNo, DbType.AnsiString, size: 50);
            parameters.Add("?CurrentlyStep", lastTaskRecord.CurrentlyStep, DbType.Int32, size: 10);
            parameters.Add("?StartTime", lastTaskRecord.StartTime, DbType.Date);
            parameters.Add("?TaskExecuterAdminId", lastTaskRecord.TaskExecuterAdminId, DbType.Int64);
            parameters.Add("?TaskExecuterFullName", lastTaskRecord.TaskExecuterFullName, DbType.AnsiString, size: 20);
            parameters.Add("?CompleteTime", lastTaskRecord.CompleteTime, DbType.DateTime);
            parameters.Add("?TaskRemark", lastTaskRecord.TaskRemark, DbType.AnsiString);
            parameters.Add("?CompanyId", lastTaskRecord.CompanyId, DbType.Int32, size: 10);
            parameters.Add("?RoleType", lastTaskRecord.RoleType.HasValue ? lastTaskRecord.RoleType.ToString() : null, DbType.AnsiString);
            parameters.Add("?CreateTime", lastTaskRecord.CreateTime, DbType.DateTime);
            parameters.Add("?TaskRecordNo", lastTaskRecord.TaskRecordNo, DbType.AnsiString, size: 50);

            await this.databaseContext.Master.ExecuteAsync(UPDATE, parameters, databaseContext.Transaction);
        }

        public async Task<(IEnumerable<TaskRecordEntity>, int)> GetPageableListAsync(string taskRecordNo, int? companyId, long? adminId, string[] workflowKeys, long? workflowActivityInstanceId, string referenceNumber, bool? isMyApproved, DateTime? startDate, DateTime? endDate, OATaskHandleStatus? status, string sortName, bool? ascending, int pageIndex, int pageSize)
        {
            string SQL_SELECT = @"SELECT oa_taskrecord.*
FROM oa_taskrecord
LEFT JOIN oa_taskreceiverrecord ON oa_taskrecord.TaskRecordNo = oa_taskreceiverrecord.TaskRecordNo
LEFT JOIN oa_workflowactivityinstancereference ON oa_taskrecord.WorkflowActivityInstanceId = oa_workflowactivityinstancereference.WorkflowActivityInstanceId
LEFT JOIN oa_workflowactivitystepinstance ON oa_taskrecord.WorkflowActivityInstanceId = oa_workflowactivitystepinstance.WorkflowActivityInstanceId AND oa_taskrecord.CurrentlyStep = oa_workflowactivitystepinstance.TaskStep";
            string SQL_COUNT = @"SELECT COUNT(1)
FROM oa_taskrecord
LEFT JOIN oa_taskreceiverrecord ON oa_taskrecord.TaskRecordNo = oa_taskreceiverrecord.TaskRecordNo
LEFT JOIN oa_workflowactivityinstancereference ON oa_taskrecord.WorkflowActivityInstanceId = oa_workflowactivityinstancereference.WorkflowActivityInstanceId
LEFT JOIN oa_workflowactivitystepinstance ON oa_taskrecord.WorkflowActivityInstanceId = oa_workflowactivitystepinstance.WorkflowActivityInstanceId AND oa_taskrecord.CurrentlyStep = oa_workflowactivitystepinstance.TaskStep";

            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();

            if (!string.IsNullOrEmpty(taskRecordNo))
            {
                wheres.Add("oa_taskrecord.TaskRecordNo = ?TaskRecordNo");
                parameters.Add("?TaskRecordNo", taskRecordNo, DbType.String);
            }

            if (companyId != null)
            {
                wheres.Add("oa_taskrecord.CompanyId >= ?CompanyId");
                parameters.Add("?CompanyId", companyId, DbType.Int32);
            }

            if (adminId != null)
            {
                wheres.Add("(oa_taskrecord.TaskExecuterAdminId = ?AdminId OR oa_taskreceiverrecord.ReceiverAdminId = ?AdminId)");
                parameters.Add("?AdminId", adminId, DbType.Int64);
            }

            if (workflowKeys != null)
            {
                if (workflowKeys.Length == 0)
                {
                    return await Task.FromResult((new List<TaskRecordEntity>(), 0));
                }

                if (workflowKeys.Length == 1)
                {
                    wheres.Add("oa_taskrecord.BusinessType = ?BusinessType");
                    parameters.Add("?BusinessType", workflowKeys.First(), DbType.String);
                }
                else
                {
                    wheres.Add("FIND_IN_SET(oa_taskrecord.BusinessType, ?BusinessTypes)");
                    parameters.Add("?BusinessTypes", string.Join(",", workflowKeys), DbType.String);
                }
            }

            if (workflowActivityInstanceId != null)
            {
                wheres.Add("oa_taskrecord.WorkflowActivityInstanceId >= ?WorkflowActivityInstanceId");
                parameters.Add("?WorkflowActivityInstanceId", workflowActivityInstanceId, DbType.Int64);
            }

            if (!string.IsNullOrEmpty(referenceNumber))
            {
                wheres.Add("oa_workflowactivityinstancereference.ReferenceNo = ?ReferenceNumber");
                parameters.Add("?ReferenceNumber", referenceNumber, DbType.String);
            }

            if (isMyApproved != null)
            {
                wheres.Add("oa_taskrecord.CurrentlyStep > 0");
            }

            if (startDate != null)
            {
                wheres.Add("oa_taskrecord.CreateTime >= ?StartDate");
                parameters.Add("?StartDate", startDate.Value.Date, DbType.DateTime);
            }

            if (endDate != null)
            {
                wheres.Add("oa_taskrecord.CreateTime < ?EndDate");
                parameters.Add("?EndDate", endDate.Value.Date.AddDays(1), DbType.DateTime);
            }

            if (status != null)
            {
                wheres.Add("oa_taskrecord.HandleStatus = ?HandleStatus");
                parameters.Add("?HandleStatus", status.Value.ToString(), DbType.String);
            }

            string whereString = null;

            if (wheres.Count > 0)
            {
                whereString = "WHERE " + string.Join(" AND ", wheres);
            }

            if (sortName == null)
            {
                sortName = "CreateTime";
            }

            string orderString = $"oa_taskrecord.{sortName}";

            if (ascending != true)
            {
                orderString += " DESC";
            }

            string sql = $@"{SQL_SELECT} {whereString} ORDER BY {orderString} LIMIT {(pageIndex - 1) * pageSize}, {pageSize}; {SQL_COUNT} {whereString}";

            return await this.databaseContext.Slave.QueryPageableListAsync<TaskRecordEntity>(sql, parameters);
        }

        public async Task<IEnumerable<TaskRecordGroupWorkflowTypeEntity>> GetPendingTaskListAsync(long adminId, RoleType roleType)
        {
            string sql = @"SELECT A.TaskExecuterAdminId,A.TaskExecuterFullName,B.WorkflowType,B.WorkflowKey,A.BusinessTypeName,COUNT(B.WorkflowType) AS TaskCount 
                        FROM OA_TaskRecord A 
                        INNER JOIN OA_WorkflowActivityInstance B ON A.WorkflowActivityInstanceId = B.WorkflowActivityInstanceId
                        WHERE A.TaskExecuterAdminId = ?TaskExecuterAdminId AND A.RoleType = ?RoleType AND (A.HandleStatus = 'Pending' OR A.HandleStatus = 'Rejected') AND B.ActivityStatus <> 'Done' 
                        GROUP BY B.WorkflowType";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskExecuterAdminId", adminId, DbType.Int64);
            parameters.Add("?RoleType", roleType.ToString(), DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<TaskRecordGroupWorkflowTypeEntity>(sql, parameters);
        }

        public async Task<IEnumerable<TaskRecordGroupWorkflowTypeEntity>> GetAppliedTaskListAsync(long adminId, RoleType roleType)
        {
            string sql = @"SELECT A.TaskExecuterAdminId,A.TaskExecuterFullName,B.WorkflowType,B.WorkflowKey,A.BusinessTypeName,COUNT(B.WorkflowType) AS TaskCount 
                        FROM OA_TaskRecord A 
                        INNER JOIN OA_WorkflowActivityInstance B ON A.WorkflowActivityInstanceId = B.WorkflowActivityInstanceId
                        WHERE A.TaskExecuterAdminId = ?TaskExecuterAdminId AND A.RoleType = ?RoleType AND B.ActivityStatus <> 'Done' AND A.CurrentlyStep = 0
                        GROUP BY B.WorkflowType";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskExecuterAdminId", adminId, DbType.Int64);
            parameters.Add("?RoleType", roleType.ToString(), DbType.AnsiString);
            return await this.databaseContext.Slave.QueryAsync<TaskRecordGroupWorkflowTypeEntity>(sql, parameters);
        }


        public async Task<(IEnumerable<TaskRecordEntity>, int)> GetTaskRecordListAsync(long? adminId, RoleType? roleType, DateTime? startDate, DateTime? endDate, OATaskHandleStatus? status, string sortName, bool? ascending, int pageIndex, int pageSize)
        {
            string SQL_SELECT = @"SELECT oa_taskrecord.* FROM oa_taskrecord
LEFT JOIN oa_taskreceiverrecord ON oa_taskrecord.TaskRecordNo = oa_taskreceiverrecord.TaskRecordNo
LEFT JOIN oa_workflowactivityinstancereference ON oa_taskrecord.WorkflowActivityInstanceId = oa_workflowactivityinstancereference.WorkflowActivityInstanceId
LEFT JOIN oa_workflowactivitystepinstance ON oa_taskrecord.WorkflowActivityInstanceId = oa_workflowactivitystepinstance.WorkflowActivityInstanceId AND oa_taskrecord.CurrentlyStep = oa_workflowactivitystepinstance.TaskStep";
            string SQL_COUNT = @"SELECT COUNT(1) FROM oa_taskrecord
LEFT JOIN oa_taskreceiverrecord ON oa_taskrecord.TaskRecordNo = oa_taskreceiverrecord.TaskRecordNo
LEFT JOIN oa_workflowactivityinstancereference ON oa_taskrecord.WorkflowActivityInstanceId = oa_workflowactivityinstancereference.WorkflowActivityInstanceId
LEFT JOIN oa_workflowactivitystepinstance ON oa_taskrecord.WorkflowActivityInstanceId = oa_workflowactivitystepinstance.WorkflowActivityInstanceId AND oa_taskrecord.CurrentlyStep = oa_workflowactivitystepinstance.TaskStep";

            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();


            if (adminId != null)
            {
                wheres.Add("(oa_taskrecord.TaskExecuterAdminId = ?AdminId OR oa_taskreceiverrecord.ReceiverAdminId = ?AdminId)");
                parameters.Add("?AdminId", adminId, DbType.Int64);
            }
            if (roleType.HasValue)
            {
                wheres.Add("oa_taskrecord.RoleType=?RoleType");
                parameters.Add("RoleType", roleType.Value, DbType.AnsiString);
            }
            if (startDate != null)
            {
                wheres.Add("oa_taskrecord.CreateTime >= ?StartDate");
                parameters.Add("?StartDate", startDate.Value.Date, DbType.DateTime);
            }

            if (endDate != null)
            {
                wheres.Add("oa_taskrecord.CreateTime < ?EndDate");
                parameters.Add("?EndDate", endDate.Value.Date.AddDays(1), DbType.DateTime);
            }

            if (status != null)
            {
                wheres.Add("oa_taskrecord.HandleStatus = ?HandleStatus");
                parameters.Add("?HandleStatus", status.Value.ToString(), DbType.String);
            }

            string whereString = null;

            if (wheres.Count > 0)
            {
                whereString = "WHERE " + string.Join(" AND ", wheres);
            }

            if (sortName == null)
            {
                sortName = "CreateTime";
            }

            string orderString = $"oa_taskrecord.{sortName}";

            if (ascending != true)
            {
                orderString += " DESC";
            }

            string sql = $@"{SQL_SELECT} {whereString} ORDER BY {orderString} LIMIT {(pageIndex - 1) * pageSize}, {pageSize}; {SQL_COUNT} {whereString}";

            return await this.databaseContext.Slave.QueryPageableListAsync<TaskRecordEntity>(sql, parameters);
        }

        public async Task<int> GetPendingTaskCountByWorkFlowKeyAsync(long adminId, RoleType roleType, string businessType)
        {
            string sql = @"SELECT COUNT(1) 
                        FROM OA_TaskRecord A 
                        INNER JOIN OA_WorkflowActivityInstance B ON A.WorkflowActivityInstanceId = B.WorkflowActivityInstanceId
                        WHERE A.TaskExecuterAdminId = ?TaskExecuterAdminId AND A.RoleType = ?RoleType AND A.HandleStatus IN ('Pending','Rejected')  
                        AND A.BusinessType = ?BusinessType";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?TaskExecuterAdminId", adminId, DbType.Int64);
            parameters.Add("?RoleType", roleType.ToString(), DbType.AnsiString);
            parameters.Add("?BusinessType", businessType, DbType.AnsiString);
            return await this.databaseContext.Slave.QueryFirstOrDefaultAsync<int>(sql, parameters);
        }

        public async Task<bool> UpdateRollBack(string currentTaskRecordNo, string backTaskRecordNo, int currentStep, long workflowActivityInstanceId)
        {
            List<string> sqls = new List<string>();
            List<DynamicParameters> parametersList = new List<DynamicParameters>();
            //1、删除最新一条记录
            string curSql = "DELETE FROM OA_TaskRecord WHERE TaskRecordNo = ?CurrentTaskRecordNo;";
            DynamicParameters curParameters = new DynamicParameters();
            curParameters.Add("?CurrentTaskRecordNo", currentTaskRecordNo, DbType.AnsiString);
            sqls.Add(curSql);
            parametersList.Add(curParameters);
            //2、更新第二条记录为Pending
            string backSql = SqlCommands.GetUpdateSql("OA_TaskRecord", new string[] { "HandleStatus", "CompleteTime" }, new string[] { "TaskRecordNo" });
            DynamicParameters backParameters = new DynamicParameters();
            backParameters.Add("?HandleStatus", OATaskHandleStatus.Pending.ToString(), DbType.AnsiString);
            backParameters.Add("?CompleteTime", null, DbType.DateTime);
            backParameters.Add("?TaskRecordNo", backTaskRecordNo, DbType.AnsiString);
            sqls.Add(backSql);
            parametersList.Add(backParameters);
            //3、更新步骤到前一步
            string activityInstanceSql = SqlCommands.GetUpdateSql("OA_WorkflowActivityInstance", new string[] { "CurrentlyStep" }, new string[] { "WorkflowActivityInstanceId" });
            DynamicParameters activityInstanceParameters = new DynamicParameters();
            activityInstanceParameters.Add("?CurrentlyStep", currentStep, DbType.Int32);
            activityInstanceParameters.Add("?WorkflowActivityInstanceId", workflowActivityInstanceId, DbType.Int64);
            sqls.Add(activityInstanceSql);
            parametersList.Add(activityInstanceParameters);
            return await this.databaseContext.Master.TransactionExecuteAsync(sqls, parametersList, databaseContext.Transaction) > 0;
        }

        public async Task<int> UpdateHandleStatus(List<string> taskRecordNoList, OATaskHandleStatus handleStatus)
        {
            string sql = "UPDATE OA_TaskRecord SET HandleStatus = ?HandleStatus,CompleteTime = ?CompleteTime WHERE FIND_IN_SET(TaskRecordNo,?TaskRecordNos)";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?HandleStatus", handleStatus.ToString(), DbType.AnsiString);
            parameters.Add("?CompleteTime", DateTime.Now, DbType.DateTime);
            parameters.Add("?TaskRecordNos", string.Join(",", taskRecordNoList), DbType.AnsiString);
            return await this.databaseContext.Master.ExecuteAsync(sql, parameters, this.databaseContext.Transaction);
        }
    }
}
